package com.kunbo.cn.service.impl.core.poundlist;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.kunbo.cn.dao.core.poundlist.PurchasePoundListDao;
import com.kunbo.cn.dao.core.poundlist.SalesPoundListDao;
import com.kunbo.cn.dao.system.BusinessCodeDao;
import com.kunbo.cn.entity.core.poundlist.PurchasePoundList;
import com.kunbo.cn.entity.core.poundlist.SalesPoundList;
import com.kunbo.cn.entity.core.vo.PurchasePoundListVo;
import com.kunbo.cn.entity.system.BusinessCode;
import com.kunbo.cn.enumeration.core.car.OrderToCarState;
import com.kunbo.cn.enumeration.core.logistics.CarState;
import com.kunbo.cn.enumeration.core.logistics.DriverState;
import com.kunbo.cn.service.core.poundlist.PurchasePoundListService;
import com.kunbo.cn.service.system.BusinessCodeService;
import com.kunbo.cn.util.NumberCovertChinese;
import com.kunbo.cn.util.common.Array;
import com.kunbo.cn.util.date.CommonsUtil;
import com.kunbo.cn.util.date.SDate;
import com.kunbo.cn.util.json.FastJsonUtils;
import com.kunbo.cn.util.retu.CommonResult;
import com.kunbo.cn.util.retu.Pages;

@Service
public class PurchasePoundListServiceImpl implements PurchasePoundListService {
	@Autowired
	private PurchasePoundListDao dao;
	
	@Autowired
	private SalesPoundListDao sdao;
	@Autowired
	private BusinessCodeDao businessCodeDao;
	
	@Autowired
	private BusinessCodeService businessCodeService;
	@Autowired
	private JdbcTemplate jdbc;
	@Transactional
	@Override
	public Map<String,Object> save(String type, Double weight) {
		Map<String,Object> result = new HashMap<String,Object>();
		//1.根据通知单号判断是否首次过磅完成
		PurchasePoundList pp = dao.findByArrivalOfGoodsNumber(type);
		SalesPoundList sp = sdao.findByBillNo(type);
		if(null!=pp || null!=sp) {
			result.put("msg","称重完成请下磅");
			return result;
		}
		//2.根据通知单号获取通知单信息
		String sql = "";
		if(type.contains("DHTZD")) {
			sql+=" select * from business_notice_of_arrival where notification_code = '"+type+"'";
			List<Map<String,Object>> list = jdbc.queryForList(sql);
			if(list.size()>0) {
				PurchasePoundList p = new PurchasePoundList();
				p.setSupplier(list.get(0).get("supplier").toString());
				p.setReceivingUnit(list.get(0).get("receiving_unit").toString());
				p.setTransport_unit(list.get(0).get("transport_unit").toString());
				//p.setMatter_type(list.get(0).get("receiving_unit").toString());
				p.setMateriel(list.get(0).get("material_name").toString());
				p.setWagonNumber(list.get(0).get("car_number").toString());
				p.setDriver(list.get(0).get("driver_name").toString());
				p.setIDNumber(list.get(0).get("card_id").toString());
				p.setRoughWeight(weight);
				p.setOldNetWeight( Double.valueOf(list.get(0).get("old_net_weight").toString()));
				p.setArrivalOfGoodsNumber(type);
				p.setReconditioningTime(SDate.getSystemDateToString());// 设置重车时间
				
				String str = "CGBD";
				Integer n = 0;
				String nStr = "";
				BusinessCode b = businessCodeDao.findByTypeAndDate(str, SDate.getSystemDateYMDToString());
				//编码表中的日期不是最新日期时则更新
				if(null==b) {
					businessCodeDao.updateNumberAndDate(1, SDate.getSystemDateYMDToString());
					b = businessCodeDao.findByTypeAndDate(str, SDate.getSystemDateYMDToString());
				}
				n = b.getNumber();
				if (n > 0 && n < 10) {
					nStr = "000" + n;
				} else if (n >= 10 && n < 100) {
					nStr = "00" + n;
				} else if (n >= 100 && n < 1000) {
					nStr = "0" + n;
				} else {
					nStr = "" + n;
				}
				p.setCgPoundNumber(str + SDate.getSystemDateYMD2ToString() + nStr);
				p.setState("1");
				p.setCreateTime(SDate.getSystemDateToString());
				dao.save(p);
				//更新磅单内码
				businessCodeService.update(str, SDate.getSystemDateYMDToString());
				//同时更新通知单的车辆状态为重车
				Integer id = Integer.parseInt(list.get(0).get("id").toString());
				jdbc.update(" update business_notice_of_arrival set notice_state = '"+OrderToCarState.zhongche+"' where id = "+id+"");
				result.put("msg", "称重完成，请下磅"+NumberCovertChinese.NumberToCharacters(p.getWagonNumber())+"毛"+weight+"吨");
				result.put("screenCarNumber", p.getWagonNumber());
				result.put("screenCarWeight", "毛"+weight+"吨");
				return result;
			}
		}else if(type.contains("THTZD")) {
			sql+=" select * from 	business_delivery_notice_manage where notice_code = '"+type+"'";
			List<Map<String,Object>> list = jdbc.queryForList(sql);
			if(list.size()>0) {
				SalesPoundList s = new SalesPoundList();
				s.setClient(list.get(0).get("receive_unit").toString());
				s.setShippingUnit(list.get(0).get("supplier").toString());
				s.setTrafficUnit(list.get(0).get("trans_unit").toString());
				//s.setMatter_type(matter_type);
				s.setMateriel(list.get(0).get("material").toString());
				s.setWagonNumber(list.get(0).get("train_number").toString());
				s.setDriver(list.get(0).get("driver").toString());
				s.setIDNumber(list.get(0).get("id_number").toString());
				s.setTare(weight);
				s.setXsOrderNumber(list.get(0).get("order_code").toString());
				s.setWaybillNumber(list.get(0).get("waybill_code").toString());
				s.setBillNo(list.get(0).get("notice_code").toString());
				s.setLightCarTime(SDate.getSystemDateToString());
				String str = "XSBD";
				Integer n = 0;
				String nStr = "";
				if(!"".equals(type) && "THTZD".equals(type)) {
					s.setLightCarTime(SDate.getSystemDateToString());//设置轻车时间
				}
				BusinessCode b = businessCodeDao.findByTypeAndDate(str, SDate.getSystemDateYMDToString());
				n = b.getNumber();
				if(n>0 && n<10) {
					nStr ="000" +n;
				}else if(n>=10 && n<100) {
					nStr ="00" +n;
				}else if(n>=100 && n<1000) {
					nStr ="0" +n;
				}else {
					nStr ="" +n;
				}
				s.setXsPoundNumber(str+SDate.getSystemDateYMD2ToString()+nStr);
				s.setState("1");
				s.setCreateTime(SDate.getSystemDateToString());
				sdao.save(s);
				businessCodeService.update(str, SDate.getSystemDateYMDToString());
				//同时更新通知单的车辆状态为空车
				Integer id = Integer.parseInt(list.get(0).get("id").toString());
                jdbc.update("update business_delivery_notice_manage set state = '"+OrderToCarState.kongche+"' where id ="+id+"");
				result.put("msg", "称重完成，请下磅"+NumberCovertChinese.NumberToCharacters(s.getWagonNumber())+"皮"+weight+"吨");
				result.put("screenCarNumber", s.getWagonNumber());
				result.put("screenCarWeight", "皮"+weight+"吨");
				return result;
			}
		}
		return result;

	}
	@Transactional
	@Override
	public Map<String,Object> update(String type, String jsonStr) {
		Map<String,Object> rm = new HashMap<String,Object>();
		PurchasePoundList t = FastJsonUtils.toBean(jsonStr, PurchasePoundList.class);
		//以BigDecimal类型包装毛重
		BigDecimal brw = new BigDecimal(String.valueOf(t.getRoughWeight()));
		//以BigDcimal类型包装毛重
		BigDecimal bt = new BigDecimal(String.valueOf(t.getTare()));
		t.setNetWeight(brw.subtract(bt).doubleValue());// 计算净重
		if (!"".equals(type) && type.contains("DHTZD")) {
			t.setLightCarTime(SDate.getSystemDateToString());// 设空车时间
		}
		t.setLightCarTime(SDate.getSystemDateToString());// 设空车时间
		t.setState("2");
		t.setLastModifiedTime(SDate.getSystemDateToString());
		dao.save(t);
		int carUpdate = jdbc.update("update vehicle_management set car_state = '" + CarState.getName(1) + "' where license_plate_number = '" + t.getWagonNumber() + "'");
		int driverUpdate = jdbc.update("update driver_management set driver_state = '" +DriverState.getName(1)+ "' where idcrad = '" + t.getIDNumber() +"'");
		String carNumber = t.getWagonNumber();
		double w = t.getNetWeight();
		PurchasePoundListVo po = new PurchasePoundListVo();
		po.setCompanyName("复兴煤业");
		po.setPoundNumber(t.getCgPoundNumber());
		po.setPoundUnit("");
		po.setReceiveUnit(t.getReceivingUnit());
		po.setPlateNumber(t.getWagonNumber());
		po.setNoticeCode(t.getArrivalOfGoodsNumber());
		po.setSpec(t.getMateriel());
		po.setRoughWeight(t.getRoughWeight());
		po.setTare(t.getTare());
		po.setNetWeight(t.getNetWeight());
		po.setLoss(null);
		po.setEnterTime(t.getReconditioningTime());
		po.setOutTime(t.getLightCarTime());
		//返回语音播报和显示屏显示的信息
		rm.put("msg", NumberCovertChinese.NumberToCharacters(carNumber)+"净"+w+"吨");
		rm.put("screenCarNumber", carNumber);
		rm.put("screenCarWeight", "净"+w+"吨");
		rm.put("purchasePound", po);
		return rm;

	}

	@Override
	public void delete(String id) {
		dao.delete(id);

	}

	@Override
	public PurchasePoundList findById(String id) {
		// TODO Auto-generated method stub
		return dao.findOne(id);
	}

	@Override
	public PurchasePoundList findByIDNumber(String iDNumber, String state) {
		// TODO Auto-generated method stub
		return dao.findByIDNumberAndState(iDNumber, state);
	}

	@Override
	public PurchasePoundList sel(String idCard) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Page<PurchasePoundList> findByPplPage(PurchasePoundList ppl, Integer pageNum, Integer pageSize) {
		if (pageNum > 0) {
			pageNum = pageNum - 1;
		}
		Pageable pageAble = new PageRequest(pageNum, pageSize, new Sort(Direction.DESC, "createTime"));
		Specification<PurchasePoundList> spec = new Specification<PurchasePoundList>() {

			@Override
			public Predicate toPredicate(Root<PurchasePoundList> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> pr = new ArrayList<>();
				if (ppl != null) {
					// pr.add(cb.like(root.get("name").as(String.class), "%"+user.getName()+"%"));

					String supplier = ppl.getSupplier();
					String cgPoundNumber = ppl.getCgPoundNumber();
					Double netWeight = ppl.getNetWeight();
					String arrivalOfGoodsNumber = ppl.getArrivalOfGoodsNumber();
					String receivingUnit = ppl.getReceivingUnit();
					String matter_type = ppl.getMatter_type();
					String materiel = ppl.getMateriel();
					if (!"".equals(matter_type)) {
						pr.add(cb.like(root.get("matter_type"), "%" + matter_type + "%"));
					}
					/*
					 * if(!"".equals(curentDate)) { pr.add(cb.like(root.get("curentDate"),
					 * "%"+curentDate+"%")); }
					 */
					// String supplier,//供应商
					// String cgPoundNumber,//过磅单号
					// Double netWeight,//净重
					// String arrivalOfGoodsNumber,//到货通知单号
					// String receivingUnit,//收货单位
					// String matter_type,
					// String materiel,
					// pr.add(cb.like(root.<String>get("name"), "%"+user.getName()+"%"));
				}
				return cb.and(pr.toArray(new Predicate[pr.size()]));
			}
		};
		return dao.findAll(spec, pageAble);

	}

	public List<Map<String, Object>> PoundList(String start_time, String end_time, String supplier,
			String cgPoundNumber, String netWeight, String receivingUnit, String arrivalOfGoodsNumber,
			String matter_type, String materiel, String wagonNumber, Integer page, Integer pageSize) {
		String sql1 = "SELECT \r\n"
				+ " p.uuid,p.cg_pound_number,p.arrival_of_goods_number,p.supplier,p.receiving_unit,p.materiel,p.wagon_number,p.rough_weight,p.tare,p.net_weight,p.old_net_weight,p.loss,p.light_car_time,p.reconditioning_time,p.handset_confirm,p.create_by,p.create_time,p.remark"
				+ " FROM \r\n" + "	db_business_purchase_pound_list p \r\n" + " WHERE   1 = 1 ";
		if (start_time != null && !start_time.isEmpty()) {
			sql1 += " and p.create_time >= '" + start_time + "'";
		}
		if (end_time != null && !end_time.isEmpty()) {
			Long cTime = CommonsUtil.stringToTime(end_time) + 86400000L;// 时间加1天
			String end = CommonsUtil.timeToString(cTime);
			sql1 += " and p.create_time < '" + end + "'";
		}
		if (supplier != null && !supplier.isEmpty()) {
			sql1 += " and p.supplier = '" + supplier + "'";
		}
		if (cgPoundNumber != null && !cgPoundNumber.isEmpty()) {
			sql1 += " and p.cg_pound_number like  '%" + cgPoundNumber + "%'";
		}
		/*
		 * String mark=">"; if("大于".equals(mark)) { mark=">"; }else
		 * if("小于".equals(mark)) { mark="<"; }else if("大于等于".equals(mark)) { mark="<";
		 * }else if("小于".equals(mark)) { mark="<"; }else if("小于等于".equals(mark)) {
		 * mark="<"; } if((netWeight != null && !netWeight.isEmpty())&&(mark != null &&
		 * !mark.isEmpty())){ sql1 += " and p.net_weight "+mark+" '" + netWeight + "'";
		 * }
		 */
		/*
		 * if(netWeight != null && !netWeight.isEmpty()){ sql1 +=
		 * " and p.net_weight = '" + netWeight + "'"; }
		 */
		if (netWeight != null && !netWeight.isEmpty()) {
			sql1 += " and p.net_weight  " + netWeight + "";
		}
		if (materiel != null && !materiel.isEmpty()) {
			sql1 += " and p.materiel = '" + materiel + "'";
		}
		if (matter_type != null && !matter_type.isEmpty()) {
			sql1 += " and p.matter_type = '" + matter_type + "'";
		}
		if (wagonNumber != null && !wagonNumber.isEmpty()) {
			sql1 += " and p.wagon_number like  '%" + wagonNumber + "%'";
		}
		if (receivingUnit != null && !receivingUnit.isEmpty()) {
			sql1 += " and p.receiving_unit = '" + receivingUnit + "'";
		}
		if (arrivalOfGoodsNumber != null && !arrivalOfGoodsNumber.isEmpty()) {
			sql1 += " and p.arrival_of_goods_number like '%" + arrivalOfGoodsNumber + "%'";
		}
		// sql1 +=" GROUP BY p.materiel \r\n";
		System.out.println("（*************sql1列表**************）" + sql1);
		// sql1 += " and p.state <> 5 and p.state <> 6 " ;
		sql1 += " order by p.create_time  desc  limit " + (page * pageSize) + "," + pageSize;
		System.out.println(sql1 + "<!!!!!!!!!!!!!!!!!!!!");
		List<Map<String, Object>> list = jdbc.queryForList(sql1);
		return list;
	}
	public Pages PoundList1(String start_time, String end_time, String supplier,
			String cgPoundNumber, String netWeight, String receivingUnit, String arrivalOfGoodsNumber,
			String matter_type, String materiel, String wagonNumber, Integer page, Integer pageSize) {
		String sql1 = "SELECT \r\n"
				+ " p.uuid,p.cg_pound_number,p.arrival_of_goods_number,p.supplier,p.receiving_unit,p.materiel,p.wagon_number,p.rough_weight,p.tare,p.net_weight,p.old_net_weight,p.loss,p.light_car_time,p.reconditioning_time,p.handset_confirm,p.create_by,p.create_time,p.remark"
				+ " FROM \r\n" + "	db_business_purchase_pound_list p \r\n" + " WHERE   1 = 1 ";
		if (start_time != null && !start_time.isEmpty()) {
			sql1 += " and p.create_time >= '" + start_time + "'";
		}
		if (end_time != null && !end_time.isEmpty()) {
			Long cTime = CommonsUtil.stringToTime(end_time) + 86400000L;// 时间加1天
			String end = CommonsUtil.timeToString(cTime);
			sql1 += " and p.create_time < '" + end + "'";
		}
		if (supplier != null && !supplier.isEmpty()) {
			sql1 += " and p.supplier = '" + supplier + "'";
		}
		if (cgPoundNumber != null && !cgPoundNumber.isEmpty()) {
			sql1 += " and p.cg_pound_number like  '%" + cgPoundNumber + "%'";
		}
		
		if (netWeight != null && !netWeight.isEmpty()) {
			sql1 += " and p.net_weight  " + netWeight + "";
		}
		if (materiel != null && !materiel.isEmpty()) {
			sql1 += " and p.materiel = '" + materiel + "'";
		}
		if (matter_type != null && !matter_type.isEmpty()) {
			sql1 += " and p.matter_type = '" + matter_type + "'";
		}
		if (wagonNumber != null && !wagonNumber.isEmpty()) {
			sql1 += " and p.wagon_number like  '%" + wagonNumber + "%'";
		}
		if (receivingUnit != null && !receivingUnit.isEmpty()) {
			sql1 += " and p.receiving_unit = '" + receivingUnit + "'";
		}
		if (arrivalOfGoodsNumber != null && !arrivalOfGoodsNumber.isEmpty()) {
			sql1 += " and p.arrival_of_goods_number like '%" + arrivalOfGoodsNumber + "%'";
		}
		// sql1 +=" GROUP BY p.materiel \r\n";
		System.out.println("（*************sql1列表**************）" + sql1);
		// sql1 += " and p.state <> 5 and p.state <> 6 " ;
		String sql2="";
		sql2 += " order by p.create_time  desc  limit " + (page * pageSize) + "," + pageSize;
		  String sql=sql1.concat(sql2);//获取数据sql
		System.out.println(sql1 + "<!!!!!!!!!!!!!!!!!!!!");
		List<Map<String, Object>> list = jdbc.queryForList(sql);
		Pages pages =new Pages();
		  Integer count = jdbc.queryForList(sql1).size();
		  pages.setTotal(count);
		  pages.setData(list);
		return pages;
	}

	public Object[][] PoundListExport(String start_time, String end_time, String supplier, String cgPoundNumber,
			String netWeight, String receivingUnit, String arrivalOfGoodsNumber, String matter_type, String materiel,
			String wagonNumber) {
		String sql1 = "SELECT \r\n"
				+ " p.cg_pound_number,p.arrival_of_goods_number,p.supplier,p.receiving_unit,p.materiel,p.wagon_number,p.rough_weight,p.tare,p.net_weight,p.old_net_weight,p.loss,p.light_car_time,p.reconditioning_time,p.handset_confirm,p.create_by,p.create_time,p.remark"
				+ " FROM \r\n" + "	db_business_purchase_pound_list p \r\n" + " WHERE   1 = 1 ";
		if (start_time != null && !start_time.isEmpty()) {
			sql1 += " and p.create_time >= '" + start_time + "'";
		}
		if (end_time != null && !end_time.isEmpty()) {
			Long cTime = CommonsUtil.stringToTime(end_time) + 86400000L;// 时间加1天
			String end = CommonsUtil.timeToString(cTime);
			sql1 += " and p.create_time < '" + end + "'";
		}
		if (supplier != null && !supplier.isEmpty()) {
			sql1 += " and p.supplier = '" + supplier + "'";
		}
		if (cgPoundNumber != null && !cgPoundNumber.isEmpty()) {
			sql1 += " and p.cg_pound_number like  '%" + cgPoundNumber + "%'";
		}
		if (netWeight != null && !netWeight.isEmpty()) {
			sql1 += " and p.net_weight  " + netWeight + "";
		}
		if (materiel != null && !materiel.isEmpty()) {
			sql1 += " and p.materiel = '" + materiel + "'";
		}
		if (matter_type != null && !matter_type.isEmpty()) {
			sql1 += " and p.matter_type = '" + matter_type + "'";
		}
		if (wagonNumber != null && !wagonNumber.isEmpty()) {
			sql1 += " and p.wagon_number like  '%" + wagonNumber + "%'";
		}
		if (receivingUnit != null && !receivingUnit.isEmpty()) {
			sql1 += " and p.receiving_unit = '" + receivingUnit + "'";
		}
		if (arrivalOfGoodsNumber != null && !arrivalOfGoodsNumber.isEmpty()) {
			sql1 += " and p.arrival_of_goods_number like '%" + arrivalOfGoodsNumber + "%'";
		}
		// sql1 +=" GROUP BY p.materiel \r\n";
		System.out.println("（*************sql1列表**************）" + sql1);
		// sql1 += " and p.state <> 5 and p.state <> 6 " ;
		sql1 += " order by p.create_time  desc   ";
		System.out.println(sql1 + "<!!!!!!!!!!!!!!!!!!!!");
		List<Map<String, Object>> list = jdbc.queryForList(sql1);
		int size = list.size();
		Object[][] array = new Object[size][17];// 数据二维数组
		String[] arr = { "过磅单号", "到货单号", "供应商", "收货单位", "物料", "车号", "毛重", "皮重", "净重", "原发净重", "亏损", "轻车时间", "重车时间",
				"手持机确认人", "制单人", "制单时间", "备注" };
		for (int i = 0; i < size; i++) {// 循环遍历所有行
			array[i] = list.get(i).values().toArray();// 每行的列数
		}
		// 表头 一维数组转进二维
		int n = 17;// 列
		int m = arr.length / n;// 行
		Object[][] b = new Object[m][n];
		int num = -1;
		for (int i = 0; i < m; i++) {
			for (int j = 0; j < n; j++) {
				num++;
				b[i][j] = arr[num];
			}
		}
		// 合并数组
		Object[][] strs3 = Array.unite(b, array);
		System.out.println("-------输出arr[]数组1------------" + Arrays.toString(arr));

		return strs3;
	}//
	public void  PoundListExports(String start_time, String end_time, String supplier, String cgPoundNumber,
			String netWeight, String receivingUnit, String arrivalOfGoodsNumber, String matter_type, String materiel,
			String wagonNumber,HttpServletResponse response) {
		String sql1 = "SELECT \r\n"
				+ " p.cg_pound_number,p.arrival_of_goods_number,p.supplier,p.receiving_unit,p.materiel,p.wagon_number,p.rough_weight,p.tare,p.net_weight,p.old_net_weight,p.loss,p.light_car_time,p.reconditioning_time,p.handset_confirm,p.create_by,p.create_time,p.remark"
				+ " FROM \r\n" + "	db_business_purchase_pound_list p \r\n" + " WHERE   1 = 1 ";
		if (start_time != null && !start_time.isEmpty()) {
			sql1 += " and p.create_time >= '" + start_time + "'";
		}
		if (end_time != null && !end_time.isEmpty()) {
			Long cTime = CommonsUtil.stringToTime(end_time) + 86400000L;// 时间加1天
			String end = CommonsUtil.timeToString(cTime);
			sql1 += " and p.create_time < '" + end + "'";
		}
		if (supplier != null && !supplier.isEmpty()) {
			sql1 += " and p.supplier = '" + supplier + "'";
		}
		if (cgPoundNumber != null && !cgPoundNumber.isEmpty()) {
			sql1 += " and p.cg_pound_number like  '%" + cgPoundNumber + "%'";
		}
		if (netWeight != null && !netWeight.isEmpty()) {
			sql1 += " and p.net_weight  " + netWeight + "";
		}
		if (materiel != null && !materiel.isEmpty()) {
			sql1 += " and p.materiel = '" + materiel + "'";
		}
		if (matter_type != null && !matter_type.isEmpty()) {
			sql1 += " and p.matter_type = '" + matter_type + "'";
		}
		if (wagonNumber != null && !wagonNumber.isEmpty()) {
			sql1 += " and p.wagon_number like  '%" + wagonNumber + "%'";
		}
		if (receivingUnit != null && !receivingUnit.isEmpty()) {
			sql1 += " and p.receiving_unit = '" + receivingUnit + "'";
		}
		if (arrivalOfGoodsNumber != null && !arrivalOfGoodsNumber.isEmpty()) {
			sql1 += " and p.arrival_of_goods_number like '%" + arrivalOfGoodsNumber + "%'";
		}
		// sql1 +=" GROUP BY p.materiel \r\n";
		System.out.println("（*************sql1列表**************）" + sql1);
		// sql1 += " and p.state <> 5 and p.state <> 6 " ;
		sql1 += " order by p.create_time  desc   ";
		System.out.println(sql1 + "<!!!!!!!!!!!!!!!!!!!!");
		List<Map<String, Object>> list = jdbc.queryForList(sql1);
		int size = list.size();
		Object[][] array = new Object[size][17];// 数据二维数组
		String[] arr = { "过磅单号", "到货单号", "供应商", "收货单位", "物料", "车号", "毛重", "皮重", "净重", "原发净重", "亏损", "轻车时间", "重车时间",
				"手持机确认人", "制单人", "制单时间", "备注" };

		//1.创建excel
		HSSFWorkbook wk = new HSSFWorkbook();
		CellStyle cs = wk.createCellStyle();
		CellStyle cs1 = wk.createCellStyle();
		cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs1.setAlignment(CellStyle.ALIGN_CENTER);
		//2.创建一张工作表
		HSSFSheet sheet = wk.createSheet();
		sheet.setColumnWidth(0, 256*20+150);
		sheet.setColumnWidth(1, 256*20+150);
		sheet.setColumnWidth(2, 256*20+150);
		sheet.setColumnWidth(3, 256*20+150);
		sheet.setColumnWidth(4, 256*18+150);
		sheet.setColumnWidth(5, 256*12+150);
		sheet.setColumnWidth(6, 256*12+150);
		sheet.setColumnWidth(7, 256*12+150);
		sheet.setColumnWidth(8, 256*12+150);
		sheet.setColumnWidth(9, 256*12+150);
		sheet.setColumnWidth(10,256*20+150);
		sheet.setColumnWidth(11,256*20+150);
		sheet.setColumnWidth(12,256*20+150);
		
		sheet.setColumnWidth(13,256*12+150);
		sheet.setColumnWidth(14,256*12+150);
		sheet.setColumnWidth(15,256*20+150);	
		sheet.setColumnWidth(16,256*20+150);
		//sheet.setColumnWidth(17,256*20+150);
		
		sheet.setDefaultRowHeightInPoints(50*50);
		
		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张A5_PAPERSIZE  A4_PAPERSIZE
		sheet.setDisplayGridlines(false);
		sheet.setPrintGridlines(false);
		sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2 ); // 右边距
		//3.设置工作表的列宽
		//sheet.setColumnWidth(0, 5000);
		//4.设置excel表头（创建第一行）行.列
		HSSFRow row0 = sheet.createRow(0);
		HSSFRow rowa = sheet.createRow(1);
		HSSFRow rowb = sheet.createRow(2);
		row0.setHeightInPoints(20);//目的是想把行高设置成20px
		//row0.getCell(0).setCellStyle(blackStyle);
		//row0.getCell(0).setCellStyle(blackStyle);
		HSSFRow row = sheet.createRow(3);
		HSSFCell cell0 = row0.createCell(0);
		HSSFCell cella = rowa.createCell(0);
		HSSFCell cellb = rowb.createCell(0);
		HSSFCell cell = row.createCell(0);
		
		// 设置字体
		CellStyle redStyle = wk.createCellStyle();
		//居中
		redStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cs1.setAlignment(CellStyle.ALIGN_LEFT);
		
		//cell.setCellStyle(redStyle);
		cell0.setCellStyle(redStyle);
		cella.setCellStyle(cs1);
		cellb.setCellStyle(cs1);
		HSSFFont font2 = wk.createFont();
		//字体
		//redFont.setFontName("宋体");
		//redStyle.setFontName("黑体");
		font2.setColor(Font.COLOR_RED);//红色
		font2.setFontName("仿宋_GB2312");
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		//设置字体大小
		font2.setFontHeightInPoints((short) 17);

		redStyle.setFont(font2);//选择需要用到的字体格式
		//cell0.setCellStyle(cs);
		
		//字段样式---》
		// 设置字体
		CellStyle css = wk.createCellStyle();

		CellStyle redStyle2 = wk.createCellStyle();
		//居中
		redStyle2.setAlignment(CellStyle.ALIGN_CENTER);
		redStyle2.setBorderLeft(CellStyle.BORDER_THIN);
		redStyle2.setBorderRight(CellStyle.BORDER_THIN);
		redStyle2.setBorderTop(CellStyle.BORDER_THIN);
		redStyle2.setBorderBottom(CellStyle.BORDER_THIN);
		HSSFFont font3 = wk.createFont();
		//字体
		font3.setColor(Font.BOLDWEIGHT_BOLD);//红色
		//font3.setFontName("仿宋_GB2312");黑体
		//font3.setFontName("黑体");
		font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
		//设置字体大小
		font3.setFontHeightInPoints((short) 13);

		redStyle2.setFont(font3);//选择需要用到的字体格式
		//————————————————————————————
		String s_time="2019-03-28 09:27:57";
		String e_time="2019-06-28 09:27:57";
		String company="复兴煤业有限公司";
		cell0.setCellValue("山西复兴煤业有限公司采购磅单表");
		cella.setCellValue("日期："+s_time+"至"+e_time);
		cellb.setCellValue("条件："+company);
	
		cell = row.createCell(0);
		//cell.setCellStyle(cs);
		
	//	String[] arr = { "过磅单号", "到货单号", "供应商", "收货单位", "物料", "车号", "毛重", "皮重", "净重", "原发净重", "亏损", "轻车时间", "重车时间",
			//	"手持机确认人", "制单人", "制单时间", "备注" };
		
		cell.setCellStyle(redStyle2);
		cell.setCellValue("过磅单号");
		
		cell = row.createCell(1);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("到货单号");
		
		cell = row.createCell(2);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("供应商");
		
		cell = row.createCell(3);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("收货单位");
		
		cell = row.createCell(4);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("物料");
		
		cell = row.createCell(5);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("车号");
		
		cell = row.createCell(6);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("毛重");
		
		cell = row.createCell(7);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("皮重");
		
		cell = row.createCell(8);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("净重");
		
		cell = row.createCell(9);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("原发净重");
		
		cell = row.createCell(10);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("亏损");
		
		cell = row.createCell(11);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("轻车时间");
		
		cell = row.createCell(12);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("重车时间");
		cell = row.createCell(13);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("手持机确认人");
		cell = row.createCell(14);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("制单人");
		cell = row.createCell(15);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("制单时间");
		cell = row.createCell(16);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("备注");
		/*cell = row.createCell(11);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("重车时间");
		cell = row.createCell(11);
		cell.setCellStyle(redStyle2);
		cell.setCellValue("重车时间");
		*/
		//5.设置excel导出数据
		 // 创建第一行
        for (short i=0;i<list.size();i++)
        {
            row = sheet.createRow(i+4);
            Cell cell1 = row.createCell(0);
            Cell cell2 = row.createCell(1);
            Cell cell3 = row.createCell(2);
            Cell cell4 = row.createCell(3);
            Cell cell5 = row.createCell(4);
            Cell cell6 = row.createCell(5);
            Cell cell7 = row.createCell(6);
            Cell cell8 = row.createCell(7);
            Cell cell9 = row.createCell(8);
            Cell cell10 = row.createCell(9);
            Cell cell11 = row.createCell(10);
            Cell cell12 = row.createCell(11);
            Cell cell13 = row.createCell(12);
            Cell cell14 = row.createCell(13);
            Cell cell15 = row.createCell(14);
            Cell cell16 = row.createCell(15);
            Cell cell17 = row.createCell(16);

              cell1.setCellValue(String.valueOf( list.get(i).get("cg_pound_number")));
              cell1.setCellStyle(cs);
              
              cell2.setCellValue(String.valueOf(list.get(i).get("arrival_of_goods_number")));
              cell2.setCellStyle(cs);
              
              cell3.setCellValue(String.valueOf(list.get(i).get("supplier")));
              cell3.setCellStyle(cs);
              
              cell4.setCellValue(String.valueOf(list.get(i).get("receiving_unit")));
              cell4.setCellStyle(cs);
              
              cell5.setCellValue(String.valueOf(list.get(i).get("materiel")));
              cell5.setCellStyle(cs);
              
              cell6.setCellValue(String.valueOf(list.get(i).get("wagon_number")));
              cell6.setCellStyle(cs);

              cell7.setCellValue(String.valueOf(list.get(i).get("rough_weight")));
              cell7.setCellStyle(cs);
              
              cell8.setCellValue(String.valueOf(list.get(i).get("tare")));
              cell8.setCellStyle(cs);
              
              cell9.setCellValue(String.valueOf(list.get(i).get("net_weight")));
              cell9.setCellStyle(cs);
              
              cell10.setCellValue(String.valueOf(list.get(i).get("old_net_weight")));
              cell10.setCellStyle(cs);

              cell11.setCellValue(String.valueOf(list.get(i).get("loss")));
              cell11.setCellStyle(cs);
              
              cell12.setCellValue(String.valueOf(list.get(i).get("light_car_time")));
              cell12.setCellStyle(cs);

              cell13.setCellValue(String.valueOf(list.get(i).get("reconditioning_time")));
              cell13.setCellStyle(cs);
              
              cell14.setCellValue(String.valueOf(list.get(i).get("handset_confirm")));
              cell14.setCellStyle(cs);
              
              cell15.setCellValue(String.valueOf(list.get(i).get("create_by")));
              cell15.setCellStyle(cs);
              
              cell16.setCellValue(String.valueOf(list.get(i).get("create_time")));
              cell16.setCellStyle(cs);
              
              cell17.setCellValue(String.valueOf(list.get(i).get("remark")));
              cell17.setCellStyle(cs);
          }
     
          CellRangeAddress  range= new CellRangeAddress(0,0,0,12);
          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$Q$2"));
          sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$Q$3"));  
          sheet.addMergedRegion(range);
  		//6.设置excel统计数据
          try {
  			//response.setContentType("application/octet-stream");
  			//response.setHeader("Content-disposition", "attachment;filename=采购磅单表.xls");//默认Excel名称
        	  String fileName="采购磅单表";
        	  response.setContentType("application/vnd.ms-excel");
        	  response.setCharacterEncoding("utf-8");
          response.setHeader("content-disposition",
                     "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+ ".xls");
  			response.flushBuffer();
  			wk.write(response.getOutputStream());
  			wk.write(new FileOutputStream(new File("D://daochu/采购磅单表明细.xls")));
  		} catch (FileNotFoundException e) {
  			e.printStackTrace();
  		} catch (IOException e) {
  			e.printStackTrace();
  		}

	}//
	public List<Map<String, Object>> findOne(String uuid) {
		String sql = "SELECT \r\n" + " p.*" + " FROM \r\n" + "	db_business_purchase_pound_list p \r\n"
				+ " WHERE   1 = 1 ";
		if (uuid != null && !uuid.isEmpty()) {
			sql += " and p.uuid = '" + uuid + "'";
		}
		List<Map<String, Object>> list = jdbc.queryForList(sql);
		return list;
	}

	@Override
	public void supplementaryIncrease(String jsonstr) {
		PurchasePoundList t = FastJsonUtils.toBean(jsonstr, PurchasePoundList.class);
		t.setCreateTime(SDate.getSystemDateToString());// 设置创建时间
		//t.setLightCarTime(SDate.getSystemDateToString());// 设空车时间
		//t.setReconditioningTime(SDate.getSystemDateToString());// 设置重车时间
		t.setState("2");
		t.setLastModifiedTime(SDate.getSystemDateToString());// 设置最后更新时间
		dao.save(t);

	}

	@Override
	public CommonResult selOrderCode(String startTime, String endTime,  String supplier,
			String material, String orderCode, Integer page, Integer pageSize) {
		// TODO Auto-generated method stub
		CommonResult result = new CommonResult();
		String sql = "select notification_code as order_code,supplier,receiving_unit,material_name,create_time from business_notice_of_arrival where 1=1";
		if (startTime != null && !startTime.equals("")) {
			sql += " and create_time >='" + startTime +"'" ;
		}
		if (endTime != null && !endTime.equals("")) {
			sql += " and create_time <='" + endTime +"'" ;
		}
		if (supplier != null && !supplier.equals("")) {
			sql += " and supplier like '%" + supplier + "%'";
		}
		if (material != null && !material.equals("")) {
			sql += " and material_name like '%" + material +"%'";
		}
		if (orderCode != null && !orderCode.equals("")) {
			sql += " and notification_code like '%" +orderCode+ "%'";
		}
		sql +=" order by id desc";
		List<Map<String,Object>> listCount = jdbc.queryForList(sql);
		sql +=" limit " + (page*pageSize) + "," + pageSize;
		List<Map<String,Object>> list = jdbc.queryForList(sql);
		result.setMsg("success");
		result.setObj(list);
		result.setCount(listCount.size());
		return result;
	}

	@Override
	public CommonResult showOhterMes(String orderCode) {
		// TODO Auto-generated method stub
		CommonResult result = new CommonResult();
		String sql = "select old_net_weight,receiving_unit,supplier,transport_unit,car_number,driver_name,material_name,card_id from business_notice_of_arrival where 1=1";
		if(orderCode != null && !orderCode.equals("")) {
			sql += " and notification_code = '" + orderCode + "'";
		}
		List<Map<String,Object>> list = jdbc.queryForList(sql);
		result.setObj(list);
		result.setMsg("SUCCESS");
		return result;
	}
	
	@Override
	public List<Map<String, Object>> getNoticeInfo() {
		String Sql = "select id,supplier,transport_unit,order_code,receiving_unit,material_name,volume_to_be_transported,accruals  from business_notice_of_arrival";
		return jdbc.queryForList(Sql);
	}

	/* (non-Javadoc)
	 * @see com.kunbo.cn.service.core.poundlist.PurchasePoundListService#carDrivers()
	 */
	@Override
	public List<Map<String, Object>> carDrivers() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void receiveConfirm(PurchasePoundList t) {
		dao.save(t);
		
	}

	

	
	
	
}
